package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.db.DBConnect;
import com.model.Resource;

public class ResourceControl {
	/**
	 * 根据物资名查询所有物资信息
	 * @param name
	 * @return
	 */
	public ArrayList<Resource> getResInformation(String name){
		ArrayList<Resource> reslist=new ArrayList<Resource>();
		DBConnect dbc=new DBConnect();
		/*
		String sql="select * from resource ";
		if(!name.equals("")){
			sql += "where res_name = '"+ name +"'";
		}
		*/
		String sql = "{call getResInformation('"+ name +"')}";
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next())
			{
				Resource res=new Resource();
				res.setRes_id(rs.getInt("res_id"));
				res.setRes_name(rs.getString("res_name"));
				res.setRes_num(rs.getInt("res_num"));
				res.setRes_des(rs.getString("res_des"));
				reslist.add(res);
			}
			return reslist;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
	}
	
	/**
	 * 更新物资信息
	 * @param res
	 * @return
	 * @throws SQLException
	 */
	public boolean updateResource(Resource res) throws SQLException{
		DBConnect dbc=new DBConnect();
		String sql="{call updateResource('"+res.getRes_num()+"'," +
				"'"+res.getRes_name()+"','"+res.getRes_des()+"',"+res.getRes_id()+")}";
		/*String sql="update resource set " +
				"res_num = "+ res.getRes_num() +", " +
				"res_name = '"+ res.getRes_name() +"', " +
				"res_des = '"+ res.getRes_des()+"' " +
				"where res_id = "+ res.getRes_id() +" ";
		*/
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 删除物资
	 * @param res_id
	 * @return
	 */
	public boolean deleteResource(int res_id) {
		DBConnect dbc=new DBConnect();
		/*
		String sql1 = "delete possession where res_id = "+ res_id +"";
		String sql2 = "delete from resource where res_id = "+ res_id +"";
		*/
		String sql = "{call deleteResource("+ res_id +")}";
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.execute();
			return true;
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("删除res失败！");
			return false;
		} finally {
			dbc.close();	
		}
	}
	/**
	 * 插入物资
	 * @param res
	 * @return
	 * @throws SQLException
	 */
	public boolean insertResource(Resource res) throws SQLException{
		DBConnect dbc=new DBConnect();
		String sql="{call insertResource('"+res.getRes_name()+"'," +
				"'"+res.getRes_num()+"','"+res.getRes_des()+"')}";
		/*String sql="insert into resource(res_name, res_num, res_des) "+
					"values('"+ res.getRes_name() +"', "+ res.getRes_num() +", '"+ res.getRes_des() +"')";
		*/
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}finally{
			dbc.close();
		}
	}
	
	/**
	 * 根据id获取物资信息
	 * @param id
	 * @return
	 */
	public Resource getResource(int id) {
		DBConnect dbc = new DBConnect();
		Resource res = new Resource();
		String sql="{call getResource("+id+")}";
		/*
		 * String sql = "select * from resource where res_id = "+ id +"";
		 */
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				res.setRes_id(rs.getInt("res_id"));
				res.setRes_name(rs.getString("res_name"));
				res.setRes_num(rs.getInt("res_num"));
				res.setRes_des(rs.getString("res_des"));
			}
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}

}
